Create Staging Table in Staging Database and Populate the Staging Tables - Continued 11
     Download the SQL File attached to the  

      Loading Excel File for other Source "Reporting District"




CREATE FUNCTION dbo.ufxCountSuspectRace
(
@J VARCHAR(6),
@SuspectRace VARCHAR(250)

)
RETURNS INT
AS
BEGIN
DECLARE @NoOfSuspectsInvolved INT, @i INT, @Count INT
DECLARE @lOffRaceActual VARCHAR(250)
SET @NoOfSuspectsInvolved = 0 
SET @i = LEN(@SuspectRace)
SET @Count = 0
WHILE @NoOfSuspectsInvolved < @i
BEGIN
SET @NoOfSuspectsInvolved = @NoOfSuspectsInvolved + 1
IF SUBSTRING(@SuspectRace,@NoOfSuspectsInvolved,LEN(@J)) = @J
BEGIN
SET @Count = @Count + 1
END
END
 
RETURN @Count;
END

SQL command Code

SELECT     [IncidentNumber],[IncidentType],[IncidentDate],
		convert(date, [IncidentDate]) as [Date], 
		convert(varchar(8), convert(time,[IncidentDate] )) as [Time],
		Year([IncidentDate]) as [Year],
		Month([IncidentDate]) as [Month],
		DateName( month , DateAdd( month , (Month([IncidentDate])) , -1 ) ) as [MonthName],
		[IncidentLocation],[City],[State],[Zip],
		[ReportingDistrict] ReportingDistrictID,b.[NAME] ReportingDistrictName,
		[GeoLocation],[HandlingUnitID],[HandlingUnitName],[NumberOfInvolvedDeputies],[DeputyRace],
		[NumberOfSuspects],[SuspectRace],
		[dbo].[ufxCountSuspectRace]('W',[SuspectRace] )  NumberOfWhiteSuspects,
		[dbo].[ufxCountSuspectRace]('B',[SuspectRace] )  NumberOfBlackSuspects,
		[dbo].[ufxCountSuspectRace]('H',[SuspectRace] ) NumberOfHispanicSuspects,
		[dbo].[ufxCountSuspectRace]('UNKNOWN',[SuspectRace] ) NumberOfUnknownSuspects,
		[NumberOfSuspectWounded],[NumberOfSuspectDeceased],
		[WeaponInvolvedCategory],[WeaponInvolvedCategoryDesc],
		[Latitude],[Longitude],[FileDate]
		
FROM	[dbo].[IncidentsExtract] a
LEFT JOIN	[dbo].[ReportingDistrict] b
ON			a.ReportingDistrict=b.[RD NUM]




Execute the Package in SSIS to load the Data into the Staging table




The Staging tables are populated with the data from the Source Files




Scalar user defined functions in SQL Server